package com.eascs.web.o2o.web.reward.service.impl;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StringUtil;
import com.eascs.site.view.BaseRestResult;
import com.eascs.web.o2o.web.reward.dao.BaseDao;
import com.eascs.web.o2o.web.reward.entity.CurLoanDetail;
import com.eascs.web.o2o.web.reward.entity.CurLoanDetailForm;
import com.eascs.web.o2o.web.reward.service.CurLoanDetailService;
import com.eascs.web.sso.User;
import com.eascs.web.sso.UserHolder;

@Service
public class CurLoanDetailServiceImpl implements CurLoanDetailService{
	
	private static Logger log = LoggerFactory.getLogger(OverdualReportServiceImpl.class);

	@Autowired
	BaseDao baseDao;
	
	@Override
	public PageResult<Object[]> getCurLoanDetailList(CurLoanDetailForm entity, String type) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(),
				entity.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		if (type.equals("search")) {
			querySql.append("select t_month,province_name,user_code,user_name,"
					+ "bill_code,loaner_name,loan_amount,loan_term,convert_loan_amount,loan_date,"
					+ "expire_date,operator_name,referrer_name,recommend_type,"
					+ "referrer_assign_coefficient,case when is_continue_loan='Y' "
					+ "then '是' when is_continue_loan='N' then '否' "
					+ "end is_continue_loan,user_convert_loan_amount,user_bill_relation "
					+ "from dm_reward_cur_loan_detail where /**DATA_PERMISSION(ltd)**/ 1=1 ");
			sqlPageQuery.setQuerySql(querySql.toString());
			if (!StringUtil.isEmpty(entity.getCheckMonth())) {
				sqlPageQuery.setQuerySql(sqlPageQuery.getQuerySql() + " and t_month=? ");
				sqlPageQuery.addParam(entity.getCheckMonth());
			} 
			if (!StringUtil.isEmpty(entity.getProvinceName())) {
				sqlPageQuery.setQuerySql(sqlPageQuery.getQuerySql() + " and province_name=?");
				sqlPageQuery.addParam(entity.getProvinceName());
			}
			if (!StringUtil.isEmpty(entity.getLoanDateStart())) {
				sqlPageQuery.setQuerySql(sqlPageQuery.getQuerySql() + " and loan_date>=?");
				sqlPageQuery.addParam(entity.getLoanDateStart());
			}
			if (!StringUtil.isEmpty(entity.getLoanDateEnd())) {
				sqlPageQuery.setQuerySql(sqlPageQuery.getQuerySql() + " and loan_date<=?");
				sqlPageQuery.addParam(entity.getLoanDateEnd());
			}
			sqlPageQuery.setQuerySql(sqlPageQuery.getQuerySql() + " order by loan_date DESC");
		} else if (type.equals("download")) {
			querySql.append("select t_month,province_code,province_name,user_no,user_code,"
					+ "user_name,bill_code,loaner_name,loan_amount,loan_term,convert_loan_amount,"
					+ "loan_date,expire_date,ltd,ltd_code,ltd_name,operator_no,operator_name,"
					+ "referrer_no,referrer_name,recommend_type,referrer_assign_coefficient,"
					+ "case when is_continue_loan='Y' then '是' "
					+ "when is_continue_loan='N' then '否' end is_continue_loan,"
					+ "user_convert_loan_amount,user_bill_relation "
					+ "from dm_reward_cur_loan_detail "
					+ "where t_month = date_format(date_sub(now(),interval 1 month),'%Y-%m') "
					+ "order by loan_date DESC");
			sqlPageQuery.setQuerySql(querySql.toString());
		}
		return baseDao.findDataWithPage(sqlPageQuery);
	}

	@Override
	@Transactional("transactionManagerDbForReward")
	public BaseRestResult<String> addCurLoanDetailList(List<List<Object>> result) {
		BaseRestResult<String> results = BaseRestResult.info("导入成功").andRefresh();
		User user=UserHolder.getUser();
		baseDao.executeSql(" delete from dm_reward_cur_loan_detail where  t_month = DATE_FORMAT(DATE_SUB(now(),INTERVAL 1 month),'%Y-%m')");
		//将excel中的数据更新到数据库中
		if(result!=null&&result.size()>0){
			for (int i = 0; i < result.size(); i++) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				List<Object> list = (List) result.get(i);
				CurLoanDetail curLoanDetail = new CurLoanDetail();
				curLoanDetail.setT_month(list.get(0).toString());
				curLoanDetail.setProvince_code(list.get(1).toString());
				curLoanDetail.setProvince_name(list.get(2).toString());
				curLoanDetail.setUser_no(list.get(3).toString());
				curLoanDetail.setUser_code(list.get(4).toString());
				curLoanDetail.setUser_name(list.get(5).toString());
				curLoanDetail.setBill_code(list.get(6).toString());
				curLoanDetail.setLoaner_name(list.get(7).toString());
				curLoanDetail.setLoan_amount((BigDecimal)list.get(8));
				curLoanDetail.setLoan_term(list.get(9).toString());
				curLoanDetail.setConvert_loan_amount((BigDecimal)list.get(10));
				curLoanDetail.setLoan_date((Date)list.get(11));
				curLoanDetail.setExpire_date((Date)list.get(12));
				curLoanDetail.setLtd(list.get(13).toString());
				curLoanDetail.setLtd_code(list.get(14).toString());
				curLoanDetail.setLtd_name(list.get(15).toString());
				curLoanDetail.setOperator_no(list.get(16).toString());
				curLoanDetail.setOperator_name(list.get(17).toString());
				curLoanDetail.setReferrer_no(list.get(18)==null ? "" : list.get(18).toString());
				curLoanDetail.setReferrer_name(list.get(19)==null ? "" : list.get(19).toString());
				curLoanDetail.setRecommend_type(list.get(20)==null ? "" : list.get(20).toString());
				curLoanDetail.setReferrer_assign_coefficient(list.get(21).toString());
				if(list.get(22).toString().contains("是") && !list.get(22).toString().contains("否")) {
					curLoanDetail.setIs_continue_loan("Y");
				} else if(list.get(22).toString().contains("否") && !list.get(22).toString().contains("是")) {
					curLoanDetail.setIs_continue_loan("N");
				}
				curLoanDetail.setUser_convert_loan_amount((BigDecimal)list.get(23));
				curLoanDetail.setUser_bill_relation(list.get(24).toString());
				curLoanDetail.setIs_lock("N");
				curLoanDetail.setCreate_user(user.getCname());
				curLoanDetail.setCreate_date(new Date());
				curLoanDetail.setUpdate_user(user.getCname());
				curLoanDetail.setUpdate_date(new Date());
				curLoanDetail.setConfirm_user(user.getCname());
				curLoanDetail.setConfirm_date(new Date());
				//curLoanDetail.setId(getIdByBillCodeAndUser(list.get(6).toString(),list.get(5).toString(),list.get(0).toString()));
				baseDao.saveEntity(curLoanDetail);
			}
		}else{
			log.error("导入的数据为空，事务回滚");
			throw new RuntimeException("导入的数据为空，请重新导入！");
		}
		return results;
	}

	public String getIdByBillCodeAndUser(String billCode, String userName, String checkMonth) {
		List<Object[]> list = baseDao.executeSqlForObjectArray("select id from dm_reward_cur_loan_detail "
				+ "where t_month='"+checkMonth+"' and bill_code='"+billCode+"' and user_name='"+userName+"'");
		return list.get(0)[0].toString();
	}
}
